﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using TruckScale.Helpers;
using static TruckScale.Helpers.LogHelper;

namespace TruckScale.Models
{
  public class CarModel
  {
    public int? CarId { get; set; }
    public string PlateNo { get; set; }
    public string VehicleWeight { get; set; }
    public string AxleCount { get; set; }
    public int? CarOwner { get; set; }
    public string Comment { get; set; }
    public bool IsValid { get; set; } = true;
    public string By1 { get; set; }
    public string By2 { get; set; }
    public string By3 { get; set; }

    public static List<CarModel> SelectAllCar()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "select * from car;";
          IEnumerable<CarModel> output = cnn.Query<CarModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CarModel> SelectCar(string plate)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from car where plate_no like '%{plate}%';";
          IEnumerable<CarModel> output = cnn.Query<CarModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CarModel> SelectActiveCar()
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "select * from car where is_valid = 1;";
          IEnumerable<CarModel> output = cnn.Query<CarModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static List<CarModel> SelectActiveCar(string plate)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from car where is_valid = 1 and plate_no like '%{plate}%';";
          IEnumerable<CarModel> output = cnn.Query<CarModel>(sql);
          return output.ToList();
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return null;
        }
      }
    }
    public static CarModel SelectUniqueCarByPlateNo(CarModel car)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from car where plate_no = '{car.PlateNo}' and car_id != '{car.CarId}';";
          CarModel output = cnn.QueryFirstOrDefault<CarModel>(sql);
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return new CarModel();
        }
      }
    }
    public static CarModel SelectCarByPlateNo(string plate)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select * from car where plate_no = '{plate}' and is_valid = 1;";
          CarModel output = cnn.QueryFirstOrDefault<CarModel>(sql);
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return new CarModel();
        }
      }
    }
    public static int SelectUsedCar(CarModel car)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"select count(*) from weighrecord where is_valid = 1 and ch = '{car.PlateNo}';";
          int output = cnn.Query<int>(sql).FirstOrDefault();
          return output;
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int InsertCar(CarModel car)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "insert into car (plate_no, vehicle_weight, axle_count, car_owner, comment) values (@PlateNo, @VehicleWeight, @AxleCount, @CarOwner, @Comment);";
          return cnn.Execute(sql, car);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int UpdateCar(CarModel car)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = "UPDATE Car SET plate_no = @PlateNo, vehicle_weight = @VehicleWeight, axle_count = @AxleCount, car_owner = @CarOwner, comment = @Comment, is_valid = @IsValid WHERE car_id = @CarId;";
          return cnn.Execute(sql, car);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int UpdateUsedCar(string plate, string oldplate)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"UPDATE weighrecord SET ch = '{plate}' WHERE ch = '{oldplate}';";
          return cnn.Execute(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
    public static int DeleteCar(CarModel car)
    {
      using (IDbConnection cnn = DBHelper.DbConnection())
      {
        try
        {
          string sql = $"delete from car where car_id = '{car.CarId}'";
          return cnn.Execute(sql);
        }
        catch (Exception e)
        {
          SetLog(e.Message, LogType.OperationLogger, LogLevel.Error);
          return -1;
        }
      }
    }
  }
}
